package test.db.util;

import static org.junit.Assert.assertTrue;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import test.com.geeksaga.util.BundleUtils;

/**
 * @author newbie
 * @since 0.1
 */
public class DBUtil {
    public static enum TYPE { MYSQL, ORACLE };

    private static Connection con = null;
    
	public static Connection getConnection() {
		Properties prop = new Properties();
		prop.put("user", BundleUtils.getString("jdbc", "jdbc.username"));
		prop.put("password", BundleUtils.getString("jdbc", "jdbc.password"));
		
        try {
        	if(con == null || (con != null && con.isClosed())) {
	            Class.forName(BundleUtils.getString("jdbc", "jdbc.driverClassName"));
	            con = DriverManager.getConnection(BundleUtils.getString("jdbc", "jdbc.url"), prop);
        	}
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return con;
	}

	public static void createTable(String query, String tableName, TYPE type) throws Exception {
        if(!existTable(tableName, type)) {
            Statement stmt = null;
            
            try {
                stmt = getConnection().createStatement();
                stmt.executeUpdate(query);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                assertTrue(releaseStatement(stmt));
            }
        }
    }
    
    public static void dropTable(String tableName, TYPE type) throws Exception {
        if(existTable(tableName, type)) {
            Statement stmt = null;
            
            try {
                stmt = getConnection().createStatement();
                
                if(type == TYPE.MYSQL)
                    stmt.executeUpdate("DROP TABLE " + tableName);
                if(type == TYPE.ORACLE)
                    stmt.executeUpdate("DROP TABLE " + tableName + " CASCADE CONSTRAINTS");
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                assertTrue(releaseStatement(stmt));
            }
        }
    }
	
    private static boolean existTable(String tableName, TYPE type) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            if(type == TYPE.MYSQL)
                pstmt = getConnection().prepareStatement("SHOW TABLES");
            if(type == TYPE.ORACLE)
                pstmt = getConnection().prepareStatement("SELECT * FROM TAB WHERE TNAME LIKE 'PW%'");
            
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                if(rs.getString(1).toUpperCase().equals(tableName.toUpperCase()))
                    return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            assertTrue(releaseResultSet(rs));
            assertTrue(releasePreparedStatement(pstmt));
        }
        
        return false;
    }
    
    public static void addData(String query) throws Exception {
        Statement stmt = null;

        try {
            stmt = getConnection().createStatement();
            stmt.executeUpdate(query);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            assertTrue(releaseStatement(stmt));
        }
    }
    
    public static List<String> selectPKData(String query, String pkLabel) throws Exception {
        Statement stmt = null;
        List<String> list = new ArrayList<String>();
        
        try {
            stmt = getConnection().createStatement();
            ResultSet rs = stmt.executeQuery(query);
            
            while (rs.next()) {
                list.add(rs.getString(pkLabel));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            assertTrue(releaseStatement(stmt));
        }
        
        return list; 
    }
    
    public static boolean releaseResultSet(ResultSet rs) {
        if(rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                
                return false;
            }
        }
        
        return true;
    }
    
    public static boolean releaseStatement(Statement stmt) {
        if(stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                
                return false;
            }
        }
        
        return true;
    }
    
    public static boolean releasePreparedStatement(PreparedStatement pstmt) {
        if(pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                
                return false;
            }
        }
        
        return true;
    }
    
    public static boolean releaseConnection(Connection con) {
        if(con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
                
                return false;
            }
        }
        
        return true;
    }
}
